package com.dkts.oil.postgis;

import com.dkts.oil.model.AisDataPoint;
import com.dkts.oil.mysql.entity.AisPoint;
import com.dkts.oil.mysql.entity.ShipTrackSegments;
import org.springframework.beans.BeanUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ShipTrackSegmentInsert {

    private static final String INSERT_SQL =
            "INSERT INTO public.ship_track_segments (" +
                    "mmsi, segment_index, start_time, end_time, " +
                    "start_lon, start_lat, end_lon, end_lat, point_count, " +
                    "avg_speed, max_speed, distance, times, uidkey, " +
                    "geom, start_point, end_point" +
                    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                    "ST_SetSRID(ST_GeomFromText(?, 4326), 4326), " +
                    "ST_SetSRID(ST_MakePoint(?, ?), 4326), " +
                    "ST_SetSRID(ST_MakePoint(?, ?), 4326))";

    /**
     * 批量插入船舶轨迹分段数据
     * @param segments 轨迹分段列表
     * @throws SQLException
     */
    public static void batchInsert(List<ShipTrackSegments> segments) throws SQLException {
        try (Connection conn = PostgresConnectionUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(INSERT_SQL)) {

            int batchSize = 0;

            for (ShipTrackSegments s : segments) {
                // 1. 基础字段
                if (s.getMmsi() != null) {
                    ps.setString(1, s.getMmsi());
                } else {
                    ps.setNull(1, Types.VARCHAR);
                }

                if (s.getSegmentIndex() != null) {
                    ps.setInt(2, s.getSegmentIndex());
                } else {
                    ps.setNull(2, Types.INTEGER);
                }

                if (s.getStartTime() != null) {
                    ps.setTimestamp(3, s.getStartTime());
                } else {
                    ps.setNull(3, Types.TIMESTAMP);
                }

                if (s.getEndTime() != null) {
                    ps.setTimestamp(4, s.getEndTime());
                } else {
                    ps.setNull(4, Types.TIMESTAMP);
                }

                if (s.getStartLon() != null) {
                    ps.setDouble(5, s.getStartLon());
                } else {
                    ps.setNull(5, Types.DOUBLE);
                }

                if (s.getStartLat() != null) {
                    ps.setDouble(6, s.getStartLat());
                } else {
                    ps.setNull(6, Types.DOUBLE);
                }

                if (s.getEndLon() != null) {
                    ps.setDouble(7, s.getEndLon());
                } else {
                    ps.setNull(7, Types.DOUBLE);
                }

                if (s.getEndLat() != null) {
                    ps.setDouble(8, s.getEndLat());
                } else {
                    ps.setNull(8, Types.DOUBLE);
                }

                if (s.getPointCount() != null) {
                    ps.setInt(9, s.getPointCount());
                } else {
                    ps.setNull(9, Types.INTEGER);
                }

                if (s.getAvgSpeed() != null) {
                    ps.setDouble(10, s.getAvgSpeed());
                } else {
                    ps.setNull(10, Types.DOUBLE);
                }

                if (s.getMaxSpeed() != null) {
                    ps.setDouble(11, s.getMaxSpeed());
                } else {
                    ps.setNull(11, Types.DOUBLE);
                }

                if (s.getDistance() != null) {
                    ps.setDouble(12, s.getDistance());
                } else {
                    ps.setNull(12, Types.DOUBLE);
                }

                if (s.getTimes() != null) {
                    ps.setDouble(13, s.getTimes());
                } else {
                    ps.setNull(13, Types.DOUBLE);
                }

                ps.setString(14, s.getUidkey());

                // 2. 构造轨迹的LINESTRING WKT
                String wkt = createLineStringWKT(s.getPoints());
                ps.setString(15, wkt);

                // 3. 起点
                if (s.getStartLon() != null) {
                    ps.setDouble(16, s.getStartLon());
                } else {
                    ps.setNull(16, Types.DOUBLE);
                }
                if (s.getStartLat() != null) {
                    ps.setDouble(17, s.getStartLat());
                } else {
                    ps.setNull(17, Types.DOUBLE);
                }

                // 4. 终点
                if (s.getEndLon() != null) {
                    ps.setDouble(18, s.getEndLon());
                } else {
                    ps.setNull(18, Types.DOUBLE);
                }
                if (s.getEndLat() != null) {
                    ps.setDouble(19, s.getEndLat());
                } else {
                    ps.setNull(19, Types.DOUBLE);
                }

                ps.addBatch();
                batchSize++;

                if (batchSize % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }

            if (batchSize % 1000 != 0) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
    }

    /**
     * 将轨迹点列表转换为 LINESTRING WKT 格式
     * @param points List of double[], 每个 double[] 形如 [lon, lat]
     * @return LINESTRING WKT 字符串
     */
    private static String createLineStringWKT(List<double[]> points) {
        if (points == null || points.isEmpty()) {
            return null;
        }
        StringBuilder sb = new StringBuilder("LINESTRING(");
        for (int i = 0; i < points.size(); i++) {
            double[] pt = points.get(i);
            sb.append(pt[0]).append(" ").append(pt[1]);
            if (i < points.size() - 1) {
                sb.append(", ");
            }
        }
        sb.append(")");
        return sb.toString();
    }

    public static void addSegments(Map<String, List<List<AisPoint>>> dataInfo,String uuid) throws SQLException {
        List<ShipTrackSegments> segments=new ArrayList<>();
        List<AisDataPoint> pointsAisData=new ArrayList<>();
        for (Map.Entry<String, List<List<AisPoint>>> entry : dataInfo.entrySet()) {
            List<List<AisPoint>> aisPoints  = entry.getValue();

            int row=aisPoints.size();
            for (int i = 0; i < row; i++) {
                ShipTrackSegments shipTrackSegments = new ShipTrackSegments();
                shipTrackSegments.setSegmentIndex(i+1);
                shipTrackSegments.setMmsi(entry.getKey());
                shipTrackSegments.setStartTime(aisPoints.get(i).get(0).getTime());
                shipTrackSegments.setEndTime(aisPoints.get(i).get(aisPoints.get(i).size()-1).getTime());
                shipTrackSegments.setStartLon(aisPoints.get(i).get(0).getLon());
                shipTrackSegments.setStartLat(aisPoints.get(i).get(0).getLat());
                shipTrackSegments.setEndLon(aisPoints.get(i).get(aisPoints.get(i).size()-1).getLon());
                shipTrackSegments.setEndLat(aisPoints.get(i).get(aisPoints.get(i).size()-1).getLat());
                shipTrackSegments.setPointCount(aisPoints.get(i).size());
                List<AisPoint> points = aisPoints.get(i);
                shipTrackSegments.setUidkey(uuid);
                double distance=0;
                //平均速度
                double totalSpeed = 0.0;
                double timeInterval=0;
                int count = 0;
                List<double[]> pointsLonLat=new ArrayList<>(1024);
                for (int j = 0; j < aisPoints.get(i).size(); j++) {
                    AisPoint point=points.get(j);

                    point.setSegmentIndex(shipTrackSegments.getSegmentIndex());
                    point.setUidkey(uuid);

                    if (point.getSpeed() != null) {
                        totalSpeed += point.getSpeed();
                        count++;
                    }
                    distance=distance+point.getDistance();
                    timeInterval=timeInterval+point.getTimeInterval();
                    pointsLonLat.add(new double[]{point.getLon(), point.getLat()});
                    AisDataPoint aisDataPoint = new AisDataPoint();
                    BeanUtils.copyProperties(point,aisDataPoint);
                    pointsAisData.add(aisDataPoint);

                }
                if (pointsLonLat.size() ==1){
                    pointsLonLat.add(new double[]{pointsLonLat.get(0)[0], pointsLonLat.get(0)[1]});
                }
                shipTrackSegments.setPoints(pointsLonLat);
                if (count > 0) {
                    shipTrackSegments.setAvgSpeed(totalSpeed / count);
                } else {
                    shipTrackSegments.setAvgSpeed(0D); // 或设置为 0.0，视业务需求而定
                }
                //保留2位小数
                shipTrackSegments.setAvgSpeed(Math.round(shipTrackSegments.getAvgSpeed() * 100) / 100.0);
                shipTrackSegments.setDistance(Math.round(distance * 100) / 100.0);
                shipTrackSegments.setTimes(Math.round(timeInterval * 100) / 100.0);
                segments.add(shipTrackSegments);
            }
        }
        batchInsert(segments);
        AisPointBatchInsert.batchInsert(pointsAisData);
    }
}
